﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Threading.Tasks;
using System.Configuration;
using System.Data.SqlClient;
using DigDes.DSchool.SUPS.DataAccess.Interface;

namespace DigDes.DSchool.SUPS.DataAccess.Database
{
    public class CargoWebFormLoader
    {

        private static readonly string ConnectionStringKey = "SUPSDB";



        public int TotalNumberOfСargos()
        {
            var connectionString = ConfigurationManager.ConnectionStrings[ConnectionStringKey].ConnectionString;

            using (var sqlConn = new SqlConnection(connectionString))
            {
                using (var sqlCmd = sqlConn.CreateCommand())
                {
                    sqlCmd.CommandText = "SELECT COUNT(*) FROM Cargo";

                    sqlConn.Open();
                    int count = System.Convert.ToInt32(sqlCmd.ExecuteScalar());
                    sqlConn.Close();
                    return count;
                }

            }
        }

        public static byte[] loadImageFromDB(int Cargo_ID)     
        {
            string connectionString = ConfigurationManager.ConnectionStrings[ConnectionStringKey].ConnectionString;
            byte[] pict = null;
            using (SqlConnection sqlConn = new SqlConnection(connectionString))
            {
                using (SqlCommand sqlCmd = new SqlCommand("SELECT Image FROM Cargo WHERE Cargo_ID = @Cargo_ID", sqlConn))
                {
                    sqlCmd.CommandType = System.Data.CommandType.Text;

 
                    sqlCmd.Parameters.AddWithValue("@Cargo_ID", Cargo_ID);
                    sqlConn.Open();
                    try
                    {
                        pict = (byte[])sqlCmd.ExecuteScalar();
                    }
                    catch (Exception e) { Console.WriteLine(e.Message); }

                    sqlConn.Close();
                }
            }
            return pict;
        }

        public List<CargoInfo> SelectCargoPaged(int startRowIndex, int PageSize)
        {
            var connectionString = ConfigurationManager.ConnectionStrings[ConnectionStringKey].ConnectionString;

            using (var sqlConn = new SqlConnection(connectionString))
            {
                using (var sqlCmd = sqlConn.CreateCommand())
                {
                    sqlCmd.CommandText = "	Select myCargo.Cargo_ID,myCargo.Name,myCargo.NameEng,myCargo.Description,myCargo.DescriptionEng,myCargo.ShortCode,myCargo.Code,myCargo.CodeGNG,myCargo.Mnemocode,myCargo.MnemocodeEng,myCargo.IsUsed,myCargo.IsEmpty FROM (SELECT * , ROW_NUMBER() OVER (ORDER BY Cargo_ID) AS RowRank FROM Cargo) AS myCargo WHERE RowRank > @startRowIndex AND RowRank <= (@startRowIndex + @maximumRows)";
                    //sqlCmd.CommandText = " dbo.SelectCargoPaged";
                    //sqlCmd.CommandType = CommandType.StoredProcedure;
                    sqlCmd.Parameters.AddWithValue("@startRowIndex", startRowIndex);
                    sqlCmd.Parameters.AddWithValue("@maximumRows", PageSize);

                    sqlConn.Open();

                    List<CargoInfo> cargo = new List<CargoInfo>();

                    using (SqlDataReader reader = sqlCmd.ExecuteReader())
                    {

                        while (reader.Read())
                        {
                            cargo.Add(GetCargoFromReader(reader));
                        }

                        if (reader != null)
                        { reader.Close(); }
                        sqlConn.Close();

                    }
                    return cargo;
                }
            }
        }

        public CargoInfo GetCargoFromReader(SqlDataReader reader)
        {
            CargoInfo cargo = new CargoInfo();

            cargo.Cargo_ID = reader["Cargo_ID"].ToString();
            cargo.Name = reader["Name"].ToString();
            cargo.NameEng = reader["NameEng"].ToString();
            cargo.Description = reader["Description"].ToString();
            cargo.DescriptionEng = reader["DescriptionEng"].ToString();
            cargo.ShortCode = reader["ShortCode"].ToString();
            cargo.Code = reader["Code"].ToString();
            cargo.CodeGNG = reader["CodeGNG"].ToString();
            cargo.Mnemocode = reader["Mnemocode"].ToString();
            cargo.MnemocodeEng = reader["MnemocodeEng"].ToString();
            cargo.IsUsed = (bool)reader["IsUsed"];
            cargo.IsEmpty = (bool)reader["IsEmpty"];

            return cargo;
        }

        protected static void checkForNulls(string Name, string NameEng, string Description, string DescriptionEng, string ShortCode, string Code, string CodeGNG, string Mnemocode, string MnemocodeEng)
        {
            if (Name == null) { Name = String.Empty; }
            if (NameEng == null) { NameEng = String.Empty; }
            if (Description == null) { Description = String.Empty; }
            if (DescriptionEng == null) { DescriptionEng = String.Empty; }
            if (ShortCode == null) { ShortCode = String.Empty; }
            if (Code == null) { Code = String.Empty; }
            if (CodeGNG == null) { CodeGNG = String.Empty; }
            if (Mnemocode == null) { Mnemocode = String.Empty; }
            if (MnemocodeEng == null) { MnemocodeEng = String.Empty; }
        }

        public CargoInfo GetSelectedCargo(string Cargo_ID)
        {
            var connectionString = ConfigurationManager.ConnectionStrings[ConnectionStringKey].ConnectionString;

            using (var sqlConn = new SqlConnection(connectionString))
            {
                using (var sqlCmd = sqlConn.CreateCommand())
                {
                    sqlCmd.CommandText = "SELECT * FROM Cargo where Cargo.Cargo_ID = @Cargo_ID";
                    //sqlCmd.CommandText = " dbo.SelectSpecificCargo";
                    //sqlCmd.CommandType = CommandType.StoredProcedure;
                    sqlCmd.Parameters.AddWithValue("@Cargo_ID", Int32.Parse(Cargo_ID));

                    CargoInfo cargo = new CargoInfo();
                    sqlConn.Open();
                    using (SqlDataReader reader = sqlCmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            cargo = GetCargoFromReader(reader);
                        }

                        if (reader != null)
                        { reader.Close(); }
                        sqlConn.Close();
                    }
                    return cargo;
                }
            }
        }

        public static void UploadImageByID(string Cargo_ID, byte[] Image)
        {
            var connectionString = ConfigurationManager.ConnectionStrings[ConnectionStringKey].ConnectionString;

            using (var sqlConn = new SqlConnection(connectionString))
            {
                using (var sqlCmd = sqlConn.CreateCommand())
                {
                    //sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                    //sqlCmd.CommandText = "InsertImageByID";
                    sqlCmd.CommandText = "UPDATE Cargo SET Image = @Image WHERE Cargo_ID = @Cargo_ID";
                    sqlCmd.Parameters.Add("@Image", SqlDbType.VarBinary).Value = Image;
                    sqlCmd.Parameters.AddWithValue("@Cargo_ID", Int32.Parse(Cargo_ID));

                    sqlConn.Open();
                    sqlCmd.ExecuteNonQuery();

                }
                sqlConn.Close();
            }
        }

        public void DeleteSelectedCargo(string Cargo_ID)
        {
            var connectionString = ConfigurationManager.ConnectionStrings[ConnectionStringKey].ConnectionString;

            using (var sqlConn = new SqlConnection(connectionString))
            {
                using (var sqlCmd = sqlConn.CreateCommand())
                {
                    //sqlCmd.CommandText = "[dbo].[DeleteCargo]";
                    //sqlCmd.CommandType = CommandType.StoredProcedure;
                    sqlCmd.CommandText = "DELETE from [Cargo] where Cargo.Cargo_ID = @Cargo_ID";
                    sqlCmd.Parameters.AddWithValue("@Cargo_ID", Int32.Parse(Cargo_ID));

                    CargoInfo cargo = new CargoInfo();
                    sqlConn.Open();
                    sqlCmd.ExecuteNonQuery();
                    sqlConn.Close();

                }
            }
        }

        public void UpdateSelectedCargo(string Cargo_ID, string Name, string NameEng, string Description, string DescriptionEng, string ShortCode, string Code, string CodeGNG, string Mnemocode, string MnemocodeEng, bool IsUsed, bool IsEmpty)
        {
            checkForNulls(Name, NameEng, Description, DescriptionEng, ShortCode, Code, CodeGNG, Mnemocode, MnemocodeEng);

            var connectionString = ConfigurationManager.ConnectionStrings[ConnectionStringKey].ConnectionString;
            using (var sqlConn = new SqlConnection(connectionString))
            {
                using (var sqlCmd = sqlConn.CreateCommand())
                {
                    sqlCmd.CommandText = "UPDATE Cargo SET Name = @Name,NameEng = @NameEng,Description = @Description,DescriptionEng = @DescriptionEng,ShortCode = @ShortCode,Code = @Code,CodeGNG = @CodeGNG, Mnemocode = @Mnemocode,MnemocodeEng = @MnemocodeEng, IsUsed = @IsUsed,IsEmpty = @IsEmpty WHERE Cargo_ID = @Cargo_ID";
                    //sqlCmd.CommandText = "[dbo].[UpdateCargo]";
                    //sqlCmd.CommandType = CommandType.StoredProcedure;

                    sqlCmd.Parameters.AddWithValue("@Name", Name);
                    sqlCmd.Parameters.AddWithValue("@NameEng", NameEng);
                    sqlCmd.Parameters.AddWithValue("@Description", Description);
                    sqlCmd.Parameters.AddWithValue("@DescriptionEng", DescriptionEng);
                    sqlCmd.Parameters.AddWithValue("@ShortCode", ShortCode);
                    sqlCmd.Parameters.AddWithValue("@Code", Code);
                    sqlCmd.Parameters.AddWithValue("@CodeGNG", CodeGNG);
                    sqlCmd.Parameters.AddWithValue("@Mnemocode", Mnemocode);
                    sqlCmd.Parameters.AddWithValue("@MnemocodeEng", MnemocodeEng);
                    sqlCmd.Parameters.AddWithValue("@IsUsed", IsUsed);
                    sqlCmd.Parameters.AddWithValue("@IsEmpty", IsEmpty);
                    sqlCmd.Parameters.AddWithValue("@Cargo_ID", Cargo_ID);
                    //sqlCmd.Parameters.Add("@Image", SqlDbType.VarBinary).Value = Image;

                    sqlConn.Open();
                    sqlCmd.ExecuteNonQuery();
                    sqlConn.Close();
                }
                return;
            }
        }

        public static void InsertCargo(string Name, string NameEng, string Description, string DescriptionEng, string ShortCode, string Code, string CodeGNG, string Mnemocode, string MnemocodeEng, bool IsUsed, bool IsEmpty)
        {
            checkForNulls(Name, NameEng, Description, DescriptionEng, ShortCode, Code, CodeGNG, Mnemocode, MnemocodeEng);

            var connectionString = ConfigurationManager.ConnectionStrings[ConnectionStringKey].ConnectionString;
            using (var sqlConn = new SqlConnection(connectionString))
            using (var sqlCmd = sqlConn.CreateCommand())
            {
                sqlCmd.CommandText = "INSERT INTO Cargo " +
                               "  (Name, NameEng, Description, DescriptionEng, ShortCode, Code, CodeGNG, Mnemocode, MnemocodeEng, IsUsed, IsEmpty) " +
                               "  Values(@Name, @NameEng, @Description, @DescriptionEng, @ShortCode, @Code, @CodeGNG, @Mnemocode, @MnemocodeEng, @IsUsed, @IsEmpty)";
                //sqlCmd.CommandText = "[dbo].[InsertCargo]";
                //sqlCmd.CommandType = CommandType.StoredProcedure;

                sqlCmd.Parameters.AddWithValue("@Name", Name);
                sqlCmd.Parameters.AddWithValue("@NameEng", NameEng);
                sqlCmd.Parameters.AddWithValue("@Description", Description);
                sqlCmd.Parameters.AddWithValue("@DescriptionEng", DescriptionEng);
                sqlCmd.Parameters.AddWithValue("@ShortCode", ShortCode);
                sqlCmd.Parameters.AddWithValue("@Code", Code);
                sqlCmd.Parameters.AddWithValue("@CodeGNG", CodeGNG);
                sqlCmd.Parameters.AddWithValue("@Mnemocode", Mnemocode);
                sqlCmd.Parameters.AddWithValue("@MnemocodeEng", MnemocodeEng);
                sqlCmd.Parameters.AddWithValue("@IsUsed", IsUsed);
                sqlCmd.Parameters.AddWithValue("@IsEmpty", IsEmpty);
                //sqlCmd.Parameters.Add("@Image", SqlDbType.VarBinary).Value = Image;

                sqlConn.Open();
                sqlCmd.ExecuteNonQuery();
                sqlConn.Close();
            }
            return;
        }


        public List<CargoInfo> GetAllCargoInfo()
        {
            var connectionString = ConfigurationManager.ConnectionStrings[ConnectionStringKey].ConnectionString;

            using (var sqlConn = new SqlConnection(connectionString))
            {

                using (var sqlCmd = sqlConn.CreateCommand())
                {
                    sqlCmd.CommandText = "Select * FROM Cargo";
                    //sqlCmd.CommandText = "[dbo].[SelectAllCargo]";
                    //sqlCmd.CommandType = CommandType.StoredProcedure;

                    List<CargoInfo> cargos = new List<CargoInfo>();
                    sqlConn.Open();
                    using (SqlDataReader reader = sqlCmd.ExecuteReader())
                    {

                        while (reader.Read())
                        {
                            cargos.Add(GetCargoFromReader(reader));
                        }

                        if (reader != null)
                        { reader.Close(); }
                        sqlConn.Close();

                    }
                    return cargos;
                }
            }
        }
    }
}

